package com.aimee.dao;

import com.aimee.entity.ExamPaper;
import com.aimee.entity.ExamPaperQuestion;
import com.aimee.entity.ExamRecord;
import com.aimee.entity.MyExam;
import com.aimee.util.Dbutil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class StuExamDAO {
    //********************************我的考试***********************************************


    //查看我的所有进行中的考试
    public List showmyexam(int user_id,String exam_state) throws SQLException {
       String sql="SELECT er.examrecord_id,e.exam_id,er.examrecord_id,er.user_id,e.exam_name,e.exampaper_id,ep.exampaper_score,ep.exampaper_passscore,er.exam_state,e.exam_time_limit,er.exam_start_date,er.exam_end_date\n" +
               "FROM `examrecord` er \n" +
               "INNER JOIN `exam_exrecord` ee ON er.`examrecord_id`=ee.`examrecord_id`\n" +
               "INNER JOIN `exam` e ON e.`exam_id`=ee.`exam_id`\n" +
               "INNER JOIN `exampaper` ep ON ep.`exampaper_id`=e.`exampaper_id`\n" +
               "WHERE er.user_id=? AND er.exam_state=?\n";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<MyExam>(MyExam.class), user_id,exam_state);
        conn.close();
        return list;

    }
    // 查询所有进行中的考试数
    public long countmyallexam(int user_id,String exam_state) throws SQLException {
        String sql = "SELECT COUNT(*)\n" +
                "FROM `examrecord` er \n" +
                "INNER JOIN `exam_exrecord` ee ON er.`examrecord_id`=ee.`examrecord_id`\n" +
                "INNER JOIN `exam` e ON e.`exam_id`=ee.`exam_id`\n" +
                "INNER JOIN `exampaper` ep ON ep.`exampaper_id`=e.`exampaper_id`\n" +
                "WHERE er.user_id=? AND er.exam_state=?";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), user_id,exam_state);
        System.out.println(count);
        conn.close();
        return count;
    }

    //按照条件查看我的考试
    public List findmyexambysth(int user_id, String starttime, String endtime) throws SQLException {
        String sql="SELECT er.examrecord_id,e.exam_id,er.examrecord_id,er.user_id,e.exam_name,e.exampaper_id,ep.exampaper_score,ep.exampaper_passscore,er.exam_state,e.exam_time_limit,er.exam_start_date,er.exam_end_date\n" +
                "FROM `examrecord` er \n" +
                "INNER JOIN `exam_exrecord` ee ON er.`examrecord_id`=ee.`examrecord_id`\n" +
                "INNER JOIN `exam` e ON e.`exam_id`=ee.`exam_id`\n" +
                "INNER JOIN `exampaper` ep ON ep.`exampaper_id`=e.`exampaper_id`\n" +
                "WHERE er.exam_start_date=? AND er.exam_end_date=? AND er.user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<MyExam>(MyExam.class),starttime, endtime,user_id);
        conn.close();
        return list;
    }

    // 查询所有试卷数
    public long countmyexambysth(int user_id,String starttime,String endtime) throws SQLException {
        String sql = "SELECT COUNT(*)\n" +
                "FROM `examrecord` er \n" +
                "INNER JOIN `exam_exrecord` ee ON er.`examrecord_id`=ee.`examrecord_id`\n" +
                "INNER JOIN `exam` e ON e.`exam_id`=ee.`exam_id`\n" +
                "INNER JOIN `exampaper` ep ON ep.`exampaper_id`=e.`exampaper_id`\n" +
                "WHERE er.exam_start_date=? AND er.exam_end_date=? AND er.user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), starttime, endtime,user_id);
        System.out.println(count);
        conn.close();
        return count;
    }


    //********************************考试记录***********************************************


    //查看我的所有考试记录
    public List showmyexamrecord(int user_id) throws SQLException {
        String sql="SELECT  er.examrecord_id,e.exam_id,er.examrecord_id,er.user_id,ep.course_id,e.exam_name,ep.exampaper_id,ep.exampaper_score,ep.exampaper_passscore,er.exam_state,e.exam_time_limit,er.exam_start_date,er.exam_end_date,er.exam_time,er.exam_usetime,er.exam_score\n" +
                "FROM examrecord er \n" +
                "INNER JOIN `exam_exrecord` ee ON er.`examrecord_id`=ee.`examrecord_id`\n" +
                "INNER JOIN `exam` e ON e.`exam_id`=ee.`exam_id`\n" +
                "INNER JOIN `exampaper` ep ON ep.`exampaper_id`=e.`exampaper_id`\n" +
                "WHERE er.user_id=? AND er.`exam_state` IN('已完成','超时未交卷')";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<ExamRecord>(ExamRecord.class), user_id);
        conn.close();
        return list;

    }
    //查看我的所有考试记录数
    public Long countallmyexamrecord(int user_id) throws SQLException {
      String sql="SELECT COUNT(*)\n" +
              "FROM `examrecord` er \n" +
              "INNER JOIN `exam_exrecord` ee ON er.`examrecord_id`=ee.`examrecord_id`\n" +
              "INNER JOIN `exam` e ON e.`exam_id`=ee.`exam_id`\n" +
              "INNER JOIN `exampaper` ep ON ep.`exampaper_id`=e.`exampaper_id`\n" +
              "WHERE er.user_id=? AND er.`exam_state` IN('已完成','超时未交卷')";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), user_id);
        System.out.println(count);
        conn.close();
        return count;
    }
    //按照条件查询我的考试记录
    public List findmyexamrecordbysth(int user_id,int course_id) throws SQLException {
        String sql="SELECT  er.examrecord_id,e.exam_id,er.examrecord_id,er.user_id,ep.course_id,e.exam_name,ep.exampaper_id,ep.exampaper_score,ep.exampaper_passscore,er.exam_state,e.exam_time_limit,er.exam_start_date,er.exam_end_date,er.exam_time,er.exam_usetime,er.exam_score\n" +
                "FROM examrecord er \n" +
                "INNER JOIN `exam_exrecord` ee ON er.`examrecord_id`=ee.`examrecord_id`\n" +
                "INNER JOIN `exam` e ON e.`exam_id`=ee.`exam_id`\n" +
                "INNER JOIN `exampaper` ep ON ep.`exampaper_id`=e.`exampaper_id`\n" +
                "WHERE er.user_id=? AND ep.course_id=? AND er.exam_state IN ('已完成','超时未交卷')";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<ExamRecord>(ExamRecord.class), user_id,course_id);
        conn.close();
        return list;

    }
    //按照条件查询我的考试记录数
    public Long countmyexamrecordbysth(int user_id,int course_id) throws SQLException {
      String sql="SELECT COUNT(*)\n" +
              "FROM examrecord er \n" +
              "INNER JOIN `exam_exrecord` ee ON er.`examrecord_id`=ee.`examrecord_id`\n" +
              "INNER JOIN `exam` e ON e.`exam_id`=ee.`exam_id`\n" +
              "INNER JOIN `exampaper` ep ON ep.`exampaper_id`=e.`exampaper_id`\n" +
              "WHERE er.user_id=? AND ep.course_id=? AND er.exam_state IN ('已完成','超时未交卷')";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), user_id,course_id);
        System.out.println(count);
        conn.close();
        return count;
    }
//查找所有的考试状态
    public List findmyexamstate()throws SQLException {
        String sql="SELECT examrecord_id,user_id,exam_state FROM`examrecord` ";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<ExamRecord>(ExamRecord.class));
        return  list;
    }
//**************试卷状态的修改*********************
    //超时未考试修改考试的状态
    public int updateexamstateifnoexam(int user_id,int exam_id) throws SQLException {
        String sql="UPDATE `examrecord` er SET exam_state='超时未交卷'\n" +
                "WHERE user_id=? AND examrecord_id IN(\n" +
                "SELECT examrecord_id FROM `exam_exrecord` WHERE exam_id=?)";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n=0;
        Object param[]=new Object[2];
        param[0]=user_id;
        param[1]=exam_id;
        n=runner.update(conn, sql,param);
        conn.close();
        return n;
    }
    //提交试卷修改试卷状态为已完成
  /*  public int updateexamstateifsubmit(int user_id,int exam_id) throws SQLException {
        String sql="UPDATE `examrecord` er SET exam_state='已完成'\n" +
                "WHERE user_id=? AND examrecord_id IN(\n" +
                "SELECT examrecord_id FROM `exam_exrecord` WHERE exam_id=?)";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n=0;
        Object param[]=new Object[2];
        param[0]=user_id;
        param[1]=exam_id;
        n=runner.update(conn, sql,param);
        conn.close();
        return n;
    }*/

    //**************提交按钮点击后的修改*********************
    //修改考试成绩 根据考试id和考试记录id int user_id
    public int updatescoreetc(int exam_score,int exam_usetime,int examrecord_id)throws SQLException {
        String sql="UPDATE `examrecord` SET exam_score=?,exam_usetime=?,exam_state='已完成'\n" +
                "WHERE examrecord_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n=0;
        Object param[]=new Object[3];
        param[0]=exam_score;
        param[1]=exam_usetime;
        param[2]=examrecord_id;
        n=runner.update(conn, sql,param);
        conn.close();
        return n;
    }

    //根据考试记录id查询特定的一场考试记录内容
    public List findexamrecordbyrecordid(int examrecord_id)throws SQLException{
        String sql="SELECT e.exam_id,er.examrecord_id,er.user_id,ep.course_id,e.exam_name,ep.exampaper_id,ep.exampaper_score,ep.exampaper_passscore,er.exam_state,e.exam_time_limit,er.exam_start_date,er.exam_end_date,er.exam_time,er.exam_usetime,er.exam_score\n" +
                "FROM examrecord er \n" +
                "INNER JOIN `exam_exrecord` ee ON er.`examrecord_id`=ee.`examrecord_id`\n" +
                "INNER JOIN `exam` e ON e.`exam_id`=ee.`exam_id`\n" +
                "INNER JOIN `exampaper` ep ON ep.`exampaper_id`=e.`exampaper_id`\n" +
                "WHERE er.`examrecord_id`=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<ExamRecord>(ExamRecord.class),examrecord_id);
        return  list;
    }

}
